const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 教师考勤列表接口
router.get('/attendance', async (req, res) => {
  try {
    const { userId, date, courseId, status } = req.query;
    
    // 获取教师ID
    const [teacherRows] = await pool.query(
      'SELECT id FROM users WHERE userId = ?',
      [userId]
    );
    
    if (teacherRows.length === 0) {
      return res.status(404).json({ message: '教师不存在' });
    }
    
    const teacherId = teacherRows[0].id;
    
    // 构建查询条件
    let whereClause = 's.teacherId = ?';
    let queryParams = [teacherId];
    
    if (date) {
      whereClause += ' AND s.date = ?';
      queryParams.push(date);
    }
    
    if (courseId) {
      whereClause += ' AND s.courseId = ?';
      queryParams.push(courseId);
    }
    
    if (status) {
      const dbStatus = status === '进行中' ? 'active' : status === '已完成' ? 'closed' : status;
      whereClause += ' AND s.status = ?';
      queryParams.push(dbStatus);
    }
    
    // 修改查询，移除不存在的字段
    const query = `
      SELECT 
        s.id,
        c.name as courseName,
        cl.className,
        s.date,
        CONCAT(DATE_FORMAT(s.startTime, '%H:%i'), '-', 
               IFNULL(DATE_FORMAT(s.endTime, '%H:%i'), '进行中')) as time,
        s.status,
        CASE 
          WHEN s.status = 'active' THEN '进行中'
          WHEN s.status = 'closed' THEN '已完成'
          ELSE s.status
        END as statusText,
        COUNT(a.id) as presentCount,
        cl.studentCount as totalCount
      FROM 
        teacher_signin_sessions s
      JOIN 
        courses c ON s.courseId = c.id
      JOIN 
        classes cl ON s.classId = cl.id
      LEFT JOIN 
        attendance a ON s.id = a.sessionId
      WHERE 
        ${whereClause}
      GROUP BY 
        s.id
      ORDER BY 
        s.date DESC, s.startTime DESC
    `;
    
    const [records] = await pool.query(query, queryParams);
    
    // 处理返回数据
    const processedRecords = records.map(record => {
      // 计算缺勤人数和请假人数（这里简化处理）
      const absentCount = record.totalCount - record.presentCount;
      // 假设请假人数为0，实际应该从请假表中查询
      const leaveCount = 0;
      
      return {
        id: record.id,
        courseName: record.courseName,
        className: record.className,
        date: record.date,
        time: record.time,
        location: '教学楼', // 提供默认值
        type: '定位签到', // 提供默认值
        status: record.statusText, // 使用statusText字段
        totalCount: record.totalCount,
        presentCount: record.presentCount,
        absentCount: absentCount,
        leaveCount: leaveCount
      };
    });
    
    // 计算统计数据
    const total = processedRecords.length;
    const ongoing = processedRecords.filter(r => r.status === '进行中').length;
    const completed = processedRecords.filter(r => r.status === '已完成').length;
    
    // 计算出勤率
    let totalStudents = 0;
    let totalPresent = 0;
    
    processedRecords.forEach(record => {
      totalStudents += record.totalCount;
      totalPresent += record.presentCount;
    });
    
    const rate = totalStudents > 0 ? (totalPresent / totalStudents * 100).toFixed(1) + '%' : '0%';
    
    res.json({
      records: processedRecords,
      stats: {
        total,
        ongoing,
        completed,
        rate
      }
    });
  } catch (error) {
    console.error('获取教师考勤列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取教师课程列表
router.get('/courses', async (req, res) => {
  try {
    const { userId } = req.query;
    
    if (!userId) {
      return res.status(400).json({ 
        success: false,
        message: '缺少必要参数' 
      });
    }
    
    // 查找教师ID
    const [userRows] = await pool.query(
      'SELECT id, role FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({ 
        success: false,
        message: '教师不存在' 
      });
    }
    
    const teacherId = userRows[0].id;
    console.log(`找到教师ID: ${teacherId}, 角色: ${userRows[0].role}`);
    
    // 简化查询，直接获取教师的课程列表
    const [courseRows] = await pool.query(`
      SELECT 
        c.id as courseId,
        c.name as courseName,
        c.weekday,
        c.startTime,
        c.endTime,
        c.teacherId
      FROM 
        courses c
      WHERE 
        c.teacherId = ?
    `, [teacherId]);
    
    console.log(`找到课程数量: ${courseRows.length}`);
    
    // 获取课程对应的班级信息
    const processedCourses = [];
    
    for (const course of courseRows) {
      // 获取课程对应的班级
      const [classRows] = await pool.query(`
        SELECT 
          cl.id as classId,
          cl.className,
          b.name as buildingName,
          cr.roomNumber
        FROM 
          course_class_relation ccr
        JOIN 
          classes cl ON ccr.classId = cl.id
        LEFT JOIN
          buildings b ON b.id = (SELECT buildingId FROM courses WHERE id = ?)
        LEFT JOIN
          classrooms cr ON cr.id = (SELECT classroomId FROM courses WHERE id = ?)
        WHERE 
          ccr.courseId = ?
      `, [course.courseId, course.courseId, course.courseId]);
      
      // 如果找到班级，添加到处理后的课程列表
      if (classRows.length > 0) {
        for (const classInfo of classRows) {
          processedCourses.push({
            courseId: course.courseId,
            courseName: course.courseName,
            classId: classInfo.classId,
            className: classInfo.className,
            location: classInfo.buildingName && classInfo.roomNumber ? 
                     `${classInfo.buildingName} - ${classInfo.roomNumber}` : '未指定地点',
            time: `周${['一','二','三','四','五','六','日'][course.weekday-1]} ${
              course.startTime ? course.startTime.slice(0,5) : '00:00'}-${
              course.endTime ? course.endTime.slice(0,5) : '00:00'}`
          });
        }
      } else {
        // 如果没有找到班级，仍然添加课程信息
        processedCourses.push({
          courseId: course.courseId,
          courseName: course.courseName,
          classId: null,
          className: '未分配班级',
          location: '未指定地点',
          time: `周${['一','二','三','四','五','六','日'][course.weekday-1]} ${
            course.startTime ? course.startTime.slice(0,5) : '00:00'}-${
            course.endTime ? course.endTime.slice(0,5) : '00:00'}`
        });
      }
    }
    
    res.json({
      success: true,
      data: processedCourses
    });
  } catch (error) {
    console.error('获取教师课程列表失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

// 获取考勤会话详情
router.get('/session/:id', async (req, res) => {
  try {
    const sessionId = req.params.id;
    const { userId } = req.query;
    
    // 查找教师ID
    const [userRows] = await pool.query(
      'SELECT id, role FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '用户不存在' });
    }
    
    if (userRows[0].role !== 'teacher') {
      return res.status(403).json({ message: '只有教师可以查看考勤详情' });
    }
    
    const teacherId = userRows[0].id;
    
    // 获取会话信息
    const [sessionRows] = await pool.query(`
      SELECT 
        s.*,
        c.name as courseName,
        cl.className
      FROM 
        teacher_signin_sessions s
      JOIN 
        courses c ON s.courseId = c.id
      JOIN 
        classes cl ON s.classId = cl.id
      WHERE 
        s.id = ? AND s.teacherId = ?
    `, [sessionId, teacherId]);
    
    if (sessionRows.length === 0) {
      return res.status(404).json({ 
        success: false,
        message: '考勤会话不存在或不属于您' 
      });
    }
    
    const session = sessionRows[0];
    
    // 获取学生签到情况 - 使用users表和classes表
    const [studentRows] = await pool.query(`
      SELECT 
        u.id,
        u.userId as studentId,
        u.name,
        a.status,
        DATE_FORMAT(a.checkInTime, '%H:%i:%s') as signTime
      FROM 
        users u
      LEFT JOIN 
        attendance a ON u.id = a.studentId AND a.sessionId = ?
      WHERE 
        u.classId = ? AND u.role = 'student'
      ORDER BY 
        u.userId
    `, [sessionId, session.classId]);
    
    // 格式化学生数据
    const students = studentRows.map(student => {
      let status = '缺勤';
      if (student.status === 'present') {
        status = '已签到';
      } else if (student.status === 'late') {
        status = '迟到';
      } else if (student.status === 'leave') {
        status = '请假';
      }
      
      return {
        ...student,
        status: status,
        signTime: student.signTime || '-'
      };
    });
    
    res.json({
      success: true,
      data: {
        session: {
          ...session,
          startTime: session.startTime ? new Date(session.startTime).toLocaleString() : null,
          endTime: session.endTime ? new Date(session.endTime).toLocaleString() : null,
          status: session.status === 'active' ? '进行中' : '已结束'
        },
        students: students
      }
    });
  } catch (error) {
    console.error('获取考勤会话详情失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

// 教师发起签到
router.post('/start-session', async (req, res) => {
  try {
    const { userId, courseId, classId, locationRequired, validDistance, location } = req.body;
    
    if (!userId || !courseId || !classId) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 查找教师ID
    const [userRows] = await pool.query(
      'SELECT id, role FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '用户不存在' });
    }
    
    if (userRows[0].role !== 'teacher') {
      return res.status(403).json({ message: '只有教师可以发起签到' });
    }
    
    const teacherId = userRows[0].id;
    const now = new Date();
    const date = now.toISOString().split('T')[0];
    const startTime = now.toISOString().slice(0, 19).replace('T', ' ');
    
    // 检查是否已有进行中的签到会话
    const [existingRows] = await pool.query(
      `SELECT * FROM teacher_signin_sessions 
       WHERE teacherId = ? AND courseId = ? AND classId = ? AND date = ? AND status = 'active'`,
      [teacherId, courseId, classId, date]
    );
    
    if (existingRows.length > 0) {
      return res.status(400).json({ message: '已有进行中的签到会话' });
    }
    
    // 创建新的签到会话
    const [result] = await pool.query(
      `INSERT INTO teacher_signin_sessions 
       (teacherId, courseId, classId, date, startTime, status, locationRequired, validDistance, 
        latitude, longitude, address) 
       VALUES (?, ?, ?, ?, ?, 'active', ?, ?, ?, ?, ?)`,
      [
        teacherId, 
        courseId, 
        classId, 
        date, 
        startTime, 
        locationRequired || true, 
        validDistance || 100,
        location ? location.latitude : null,
        location ? location.longitude : null,
        location ? location.address : null
      ]
    );
    
    res.status(201).json({ 
      message: '签到会话已创建',
      sessionId: result.insertId
    });
  } catch (error) {
    console.error('创建签到会话失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 教师结束签到
router.post('/end-session', async (req, res) => {
  try {
    const { userId, sessionId } = req.body;
    
    if (!userId || !sessionId) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 查找教师ID
    const [userRows] = await pool.query(
      'SELECT id, role FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '用户不存在' });
    }
    
    if (userRows[0].role !== 'teacher') {
      return res.status(403).json({ message: '只有教师可以结束签到' });
    }
    
    const teacherId = userRows[0].id;
    const now = new Date();
    const endTime = now.toISOString().slice(0, 19).replace('T', ' ');
    
    // 更新签到会话状态
    const [result] = await pool.query(
      `UPDATE teacher_signin_sessions 
       SET status = 'closed', endTime = ? 
       WHERE id = ? AND teacherId = ? AND status = 'active'`,
      [endTime, sessionId, teacherId]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到有效的签到会话或无权限结束该会话' });
    }
    
    res.json({ message: '签到会话已结束' });
  } catch (error) {
    console.error('结束签到会话失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 更新考勤状态
router.put('/attendance/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    
    // 更新考勤状态
    await pool.query(
      'UPDATE teacher_signin_sessions SET status = ?, endTime = ? WHERE id = ?',
      [status, status === 'closed' ? new Date() : null, id]
    );
    
    res.json({ success: true, message: '状态更新成功' });
  } catch (error) {
    console.error('更新考勤状态失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取考勤详情
router.get('/attendance/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // 获取考勤基本信息
    const [sessionRows] = await pool.query(`
      SELECT 
        s.id,
        c.name as courseName,
        cl.className,
        s.date,
        DATE_FORMAT(s.date, '%Y-%m-%d') as formattedDate,
        DATE_FORMAT(s.startTime, '%H:%i') as startTime,
        DATE_FORMAT(s.endTime, '%H:%i') as endTime,
        c.buildingId,
        c.classroomId,
        CASE 
          WHEN s.status = 'active' THEN '进行中'
          WHEN s.status = 'closed' THEN '已完成'
          ELSE s.status
        END as status,
        DATE_FORMAT(s.startTime, '%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(s.endTime, '%Y-%m-%d %H:%i:%s') as updateTime
      FROM 
        teacher_signin_sessions s
      JOIN 
        courses c ON s.courseId = c.id
      JOIN 
        classes cl ON s.classId = cl.id
      WHERE 
        s.id = ?
    `, [id]);

    if (sessionRows.length === 0) {
      return res.status(404).json({ message: '考勤记录不存在' });
    }

    // 获取教学楼和教室信息
    const [locationInfo] = await pool.query(`
      SELECT 
        b.name as buildingName,
        cr.roomNumber
      FROM 
        buildings b
      JOIN 
        classrooms cr ON cr.buildingId = b.id
      WHERE 
        b.id = ? AND cr.id = ?
    `, [sessionRows[0].buildingId, sessionRows[0].classroomId]);

    // 获取学生签到情况
    const [studentRows] = await pool.query(`
      SELECT 
        u.userId as studentId,
        u.name,
        CASE
          WHEN a.status IS NOT NULL THEN a.status
          ELSE 'absent'
        END as status,
        DATE_FORMAT(a.checkInTime, '%H:%i:%s') as checkInTime,
        DATE_FORMAT(a.checkOutTime, '%H:%i:%s') as checkOutTime
      FROM 
        users u
      LEFT JOIN 
        attendance a ON a.sessionId = ? AND a.studentId = u.id
      WHERE 
        u.classId = (
          SELECT classId FROM teacher_signin_sessions WHERE id = ?
        )
        AND u.role = 'student'
      ORDER BY 
        u.userId
    `, [id, id]);

    // 处理学生数据，转换状态为中文
    const processedStudentRows = studentRows.map(student => ({
      ...student,
      status: student.status === 'present' ? '已签到' :
              student.status === 'late' ? '迟到' :
              student.status === 'leave' ? '请假' : '缺勤',
      checkInTime: student.checkInTime || '-',
      checkOutTime: student.checkOutTime || '-'
    }));

    // 计算统计数据
    const stats = {
      total: studentRows.length,
      present: studentRows.filter(s => s.status === 'present').length,
      absent: studentRows.filter(s => s.status === 'absent').length,
      late: studentRows.filter(s => s.status === 'late').length,
      leave: 0  // 暂时不统计请假人数
    };

    // 合并位置信息
    const attendanceInfo = {
      ...sessionRows[0],
      location: locationInfo.length > 0 
        ? `${locationInfo[0].buildingName} - ${locationInfo[0].roomNumber}`
        : '未指定地点'
    };

    res.json({
      success: true,
      data: {
        attendanceInfo,
        studentList: processedStudentRows,  // 使用处理后的学生数据
        stats
      }
    });
    
  } catch (error) {
    console.error('获取考勤详情失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 导出考勤记录
router.get('/attendance/:id/export', async (req, res) => {
  try {
    const { id } = req.params;
    
    // 获取考勤基本信息
    const [sessionRows] = await pool.query(`
      SELECT 
        s.id,
        c.name as courseName,
        cl.className,
        s.date,
        DATE_FORMAT(s.startTime, '%H:%i') as startTime,
        DATE_FORMAT(s.endTime, '%H:%i') as endTime,
        c.buildingId,
        c.classroomId,
        CASE 
          WHEN s.status = 'active' THEN '进行中'
          WHEN s.status = 'closed' THEN '已完成'
          ELSE s.status
        END as status,
        DATE_FORMAT(s.startTime, '%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(s.endTime, '%Y-%m-%d %H:%i:%s') as updateTime
      FROM 
        teacher_signin_sessions s
      JOIN 
        courses c ON s.courseId = c.id
      JOIN 
        classes cl ON s.classId = cl.id
      WHERE 
        s.id = ?
    `, [id]);

    if (sessionRows.length === 0) {
      return res.status(404).json({ message: '考勤记录不存在' });
    }

    // 获取教学楼和教室信息
    const [locationInfo] = await pool.query(`
      SELECT 
        b.name as buildingName,
        cr.roomNumber
      FROM 
        buildings b
      JOIN 
        classrooms cr ON cr.buildingId = b.id
      WHERE 
        b.id = ? AND cr.id = ?
    `, [sessionRows[0].buildingId, sessionRows[0].classroomId]);

    // 获取学生签到情况
    const [studentRows] = await pool.query(`
      SELECT 
        u.userId as studentId,
        u.name,
        CASE
          WHEN a.status IS NOT NULL THEN a.status
          ELSE 'absent'
        END as status,
        DATE_FORMAT(a.checkInTime, '%H:%i:%s') as checkInTime,
        DATE_FORMAT(a.checkOutTime, '%H:%i:%s') as checkOutTime
      FROM 
        users u
      LEFT JOIN 
        attendance a ON a.sessionId = ? AND a.studentId = u.id
      WHERE 
        u.classId = (
          SELECT classId FROM teacher_signin_sessions WHERE id = ?
        )
        AND u.role = 'student'
      ORDER BY 
        u.userId
    `, [id, id]);

    // 处理学生数据，转换状态为中文
    const processedStudentRows = studentRows.map(student => ({
      ...student,
      status: student.status === 'present' ? '已签到' :
              student.status === 'late' ? '迟到' :
              student.status === 'leave' ? '请假' : '缺勤',
      checkInTime: student.checkInTime || '-',
      checkOutTime: student.checkOutTime || '-'
    }));

    // 计算统计数据
    const stats = {
      total: studentRows.length,
      present: studentRows.filter(s => s.status === 'present').length,
      absent: studentRows.filter(s => s.status === 'absent').length,
      late: studentRows.filter(s => s.status === 'late').length,
      leave: studentRows.filter(s => s.status === 'leave').length
    };

    // 合并位置信息
    const attendanceInfo = {
      ...sessionRows[0],
      location: locationInfo.length > 0 
        ? `${locationInfo[0].buildingName} - ${locationInfo[0].roomNumber}`
        : '未指定地点'
    };

    // 创建Excel文件
    const ExcelJS = require('exceljs');
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('考勤记录');
    
    // 设置表头和样式
    worksheet.columns = [
      { header: '学号', key: 'studentId', width: 15 },
      { header: '姓名', key: 'name', width: 15 },
      { header: '状态', key: 'status', width: 15 },
      { header: '签到时间', key: 'checkInTime', width: 20 }
    ];
    
    // 添加考勤信息标题
    worksheet.mergeCells('A1:D1');
    const titleCell = worksheet.getCell('A1');
    titleCell.value = `${attendanceInfo.courseName} - ${attendanceInfo.className} 考勤记录`;
    titleCell.font = { size: 16, bold: true };
    titleCell.alignment = { horizontal: 'center' };
    
    // 添加考勤日期和时间
    worksheet.mergeCells('A2:D2');
    const dateCell = worksheet.getCell('A2');
    dateCell.value = `日期: ${attendanceInfo.date}  时间: ${attendanceInfo.startTime}-${attendanceInfo.endTime || '进行中'}`;
    dateCell.alignment = { horizontal: 'center' };
    
    // 添加统计信息
    worksheet.mergeCells('A3:D3');
    const statsCell = worksheet.getCell('A3');
    statsCell.value = `总人数: ${stats.total}  已签到: ${stats.present}  缺勤: ${stats.absent}  迟到: ${stats.late}  请假: ${stats.leave}`;
    statsCell.alignment = { horizontal: 'center' };
    
    // 设置表头样式
    worksheet.getRow(4).font = { bold: true };
    worksheet.getRow(4).alignment = { horizontal: 'center' };
    
    // 添加学生数据
    processedStudentRows.forEach(student => {
      worksheet.addRow({
        studentId: student.studentId,
        name: student.name,
        status: student.status,
        checkInTime: student.checkInTime
      });
    });
    
    // 设置所有单元格居中
    for (let i = 5; i <= processedStudentRows.length + 4; i++) {
      worksheet.getRow(i).alignment = { horizontal: 'center' };
    }
    
    // 生成文件名 - 使用date字段，并格式化为没有分隔符的日期
    const formattedDate = String(attendanceInfo.date).replace(/-/g, '');
    const fileName = `考勤记录_${attendanceInfo.courseName}_${formattedDate}.xlsx`;
    
    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    
    // 将工作簿写入响应
    await workbook.xlsx.write(res);
    res.end();
    
  } catch (error) {
    console.error('导出考勤记录失败:', error);
    res.status(500).json({ success: false, message: '导出考勤记录失败' });
  }
});

// 获取教师考勤会话列表
router.get('/attendance-sessions', auth, async (req, res) => {
  try {
    const { userId, date, courseId, status } = req.query;
    
    console.log('获取教师考勤会话，参数:', { userId, date, courseId, status });
    
    if (!userId) {
      return res.status(400).json({ 
        success: false, 
        message: '缺少教师ID参数' 
      });
    }
    
    // 查找教师ID
    const [userRows] = await pool.query(
      'SELECT id FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({ 
        success: false, 
        message: '教师不存在' 
      });
    }
    
    const teacherId = userRows[0].id;
    
    // 构建查询条件
    let query = `
      SELECT 
        s.id,
        s.courseId,
        s.classId,
        s.startTime,
        s.endTime,
        s.status,
        s.latitude,
        s.longitude,
        s.address,
        c.name as courseName,
        cl.className,
        DATE(s.startTime) as date
      FROM teacher_signin_sessions s
      LEFT JOIN courses c ON s.courseId = c.id
      LEFT JOIN classes cl ON s.classId = cl.id
      WHERE s.teacherId = ?
    `;
    
    const params = [teacherId];
    
    // 添加日期过滤
    if (date) {
      query += ` AND DATE(s.startTime) = ?`;
      params.push(date);
    }
    
    // 添加课程过滤
    if (courseId && courseId !== '0') {
      query += ` AND s.courseId = ?`;
      params.push(courseId);
    }
    
    // 添加状态过滤
    if (status && status !== '全部') {
      let dbStatus = '';
      switch (status) {
        case '进行中':
          dbStatus = 'active';
          break;
        case '已结束':
          dbStatus = 'closed';
          break;
      }
      
      if (dbStatus) {
        query += ` AND s.status = ?`;
        params.push(dbStatus);
      }
    }
    
    // 按开始时间降序排序
    query += ` ORDER BY s.startTime DESC`;
    
    console.log('执行SQL查询:', query, params);
    
    // 执行查询
    const [sessions] = await pool.execute(query, params);
    
    // 格式化会话数据
    const formattedSessions = sessions.map(session => {
      // 格式化开始时间
      const startTime = new Date(session.startTime);
      const formattedStartTime = `${startTime.getHours().toString().padStart(2, '0')}:${startTime.getMinutes().toString().padStart(2, '0')}`;
      
      // 格式化结束时间
      const endTime = session.endTime ? new Date(session.endTime) : null;
      const formattedEndTime = endTime ? 
        `${endTime.getHours().toString().padStart(2, '0')}:${endTime.getMinutes().toString().padStart(2, '0')}` : 
        '进行中';
      
      // 格式化状态
      let statusText = '';
      switch (session.status) {
        case 'active':
          statusText = '进行中';
          break;
        case 'closed':
          statusText = '已结束';
          break;
        default:
          statusText = session.status;
      }
      
      // 解析位置信息
      let location = '教学楼'; // 默认位置
      if (session.address) {
        location = session.address;
      }
      
      return {
        id: session.id,
        courseId: session.courseId,
        classId: session.classId,
        courseName: session.courseName || '未知课程',
        className: session.className || '未知班级',
        date: session.date,
        startTime: formattedStartTime,
        endTime: formattedEndTime,
        time: `${formattedStartTime}-${formattedEndTime === '进行中' ? formattedEndTime : formattedEndTime}`,
        location: location,
        type: '定位签到', // 默认签到类型
        status: statusText,
        // 默认统计数据，后续可以从实际签到记录中获取
        totalCount: 0,
        presentCount: 0,
        absentCount: 0,
        leaveCount: 0
      };
    });
    
    // 获取每个会话的签到统计
    for (let i = 0; i < formattedSessions.length; i++) {
      const session = formattedSessions[i];
      
      // 获取班级总人数
      const [classStudents] = await pool.execute(
        `SELECT COUNT(*) as total FROM users WHERE classId = ? AND role = 'student'`,
        [session.classId]
      );
      
      session.totalCount = classStudents[0].total || 0;
      
      // 获取已签到人数
      const [presentStudents] = await pool.execute(
        `SELECT COUNT(*) as count FROM attendance WHERE sessionId = ? AND status IN ('present', 'late')`,
        [session.id]
      );
      
      session.presentCount = presentStudents[0].count || 0;
      
      // 获取请假人数
      const [leaveStudents] = await pool.execute(
        `SELECT COUNT(*) as count FROM attendance WHERE sessionId = ? AND status = 'leave'`,
        [session.id]
      );
      
      session.leaveCount = leaveStudents[0].count || 0;
      
      // 计算缺勤人数
      session.absentCount = session.totalCount - session.presentCount - session.leaveCount;
      if (session.absentCount < 0) session.absentCount = 0;
    }
    
    // 计算统计数据
    const total = formattedSessions.length;
    const ongoing = formattedSessions.filter(s => s.status === '进行中').length;
    const completed = formattedSessions.filter(s => s.status === '已结束').length;
    
    // 计算出勤率
    let totalStudents = 0;
    let totalPresent = 0;
    
    formattedSessions.forEach(session => {
      totalStudents += session.totalCount;
      totalPresent += session.presentCount;
    });
    
    const rate = totalStudents > 0 ? (totalPresent / totalStudents * 100).toFixed(1) + '%' : '0%';
    
    res.json({
      success: true,
      sessions: formattedSessions,
      stats: {
        total,
        ongoing,
        completed,
        rate
      }
    });
    
  } catch (error) {
    console.error('获取教师考勤会话失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message 
    });
  }
});
// 获取班级考勤统计
router.get('/class-stats', async (req, res) => {
  try {
    const { classId, courseId, date } = req.query;
    
    console.log('获取班级考勤统计，参数:', { classId, courseId, date });
    
    if (!classId || !courseId) {
      return res.status(400).json({ 
        success: false, 
        message: '缺少必要参数' 
      });
    }
    
    // 处理日期参数，将ISO格式转换为MySQL日期格式
    let formattedDate = null;
    if (date) {
      formattedDate = new Date(date).toISOString().split('T')[0];
    }
    
    // 查询班级考勤统计 - 修复SQL语法错误，使用反引号包裹保留关键字
    const query = `
      SELECT 
        COUNT(DISTINCT s.id) as totalSessions,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as presentCount,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absentCount,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as lateCount,
        SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leaveCount\`
      FROM 
        teacher_signin_sessions s
      LEFT JOIN 
        attendance a ON s.id = a.sessionId
      WHERE 
        s.classId = ?
        AND s.courseId = ?
        ${formattedDate ? 'AND DATE(s.startTime) = ?' : ''}
    `;
    
    const params = [classId, courseId];
    if (formattedDate) {
      params.push(formattedDate);
    }
    
    console.log('执行SQL查询:', query, params);
    
    const [results] = await pool.execute(query, params);
    
    // 获取班级学生总数
    const [classInfo] = await pool.execute(
      `SELECT COUNT(*) as studentCount FROM users WHERE classId = ? AND role = 'student'`,
      [classId]
    );
    
    const studentCount = classInfo[0].studentCount || 0;
    
    // 计算出勤率
    const totalAttendances = parseInt(results[0].presentCount || 0) + parseInt(results[0].lateCount || 0);
    const totalPossibleAttendances = parseInt(results[0].totalSessions || 0) * studentCount;
    const attendanceRate = totalPossibleAttendances > 0 
      ? (totalAttendances / totalPossibleAttendances * 100).toFixed(1) + '%' 
      : '0%';
    
    // 获取班级和课程信息
    const [classDetails] = await pool.execute(
      `SELECT className FROM classes WHERE id = ?`,
      [classId]
    );
    
    const [courseDetails] = await pool.execute(
      `SELECT name as courseName FROM courses WHERE id = ?`,
      [courseId]
    );
    
    res.json({
      success: true,
      data: {
        className: classDetails[0]?.className || '未知班级',
        courseName: courseDetails[0]?.courseName || '未知课程',
        date: formattedDate || '全部日期',
        stats: {
          studentCount,
          totalSessions: parseInt(results[0].totalSessions || 0),
          presentCount: parseInt(results[0].presentCount || 0),
          absentCount: parseInt(results[0].absentCount || 0),
          lateCount: parseInt(results[0].lateCount || 0),
          leaveCount: parseInt(results[0].leaveCount || 0),
          attendanceRate
        }
      }
    });
    
  } catch (error) {
    console.error('获取班级考勤统计失败:', error);
    res.status(500).json({ 
      success: false, 
      message: '服务器错误',
      error: error.message 
    });
  }
});
module.exports = router;